CREATE PROC [dbo].[EPMCalculateSupplementaryInsurance]
    @PrescriptionType TINYINT,
    @InsuranceCode VARCHAR(3),
    @FormulaDefinition VARCHAR(MAX) = NULL,
    @PrescriptionId UNIQUEIDENTIFIER,
    @PyramidPrescriptionTempId INT = NULL
AS
BEGIN
    /* ----------------- Last Update 03-08-07 ----------------- */
    BEGIN TRY
        /*--------------------------------------------------------------------------------------- Declare Variable*/
        DECLARE @NonInsuranceFlag BIT;
        DECLARE @PatientFlag BIT;
        DECLARE @GoodsFlag BIT;
        DECLARE @DifferFlag BIT;
        DECLARE @SupplementaryTransactionId SMALLINT;
        DECLARE @IncurableTransactionId SMALLINT;
        DECLARE @DefaultTaxPercentage TINYINT;
        DECLARE @TaxTransactionId SMALLINT;
        DECLARE @TaxTransactionName NVARCHAR(MAX);
        /*--------------------------------------------------------------------------------------- Get Tamin Incurable Transaction Id */
        IF EXISTS
        (
            SELECT 1
            FROM dbo.GeneralSetting
            WHERE Parameter = 'TAMIN_Settings_ActiveSaveSupport'
                  AND Value = 'True'
        )
           AND @PrescriptionType IN ( 0, 4 )
            SELECT @IncurableTransactionId = CAST(ISNULL(Value, 0) AS SMALLINT)
            FROM dbo.GeneralSetting
            WHERE Parameter = 'TAMIN_Settings_TransactionCodeSupport';
        /*--------------------------------------------------------------------------------------- Declare Prescription Detail*/
        DECLARE @PrescriptionDetail AS TABLE
        (
            Id UNIQUEIDENTIFIER NOT NULL,
            RowIndex TINYINT
                DEFAULT (1) NULL,
            GoodsCode VARCHAR(15) NOT NULL,
            GenericCode VARCHAR(6) NULL,
            IRCCode VARCHAR(20) NULL,
            InsuredFranshiz FLOAT
                DEFAULT (0),         /* Insured Franshiz Percent */
            StatusCode TINYINT,
            CoverCount FLOAT,        /*insurance Coverd Count Amount*/
            DeliverCount FLOAT,
            SalesPriceAmount MONEY,  /* Sales Price Fee */
            IncurablePaidAmount MONEY,
            InsuredAmount MONEY,     /* Insurer Insured Amount */
            NonInsuranceAmount MONEY,
            EquipmentAmount MONEY,
            GoodsAmount MONEY,
            DifferenceAmount MONEY,
            InsuredPaidAmount MONEY, /* Total Insured Pay Amount ( Insured Amount + Differ Amount )*/
            TotalPrice MONEY,        /* Pyramid Total Sales Price Amount  */
            IsIncludeSupplementaryInsurance BIT
                DEFAULT (1),
            HasDuty BIT
                DEFAULT (0)
        );
        /*--------------------------------------------------------------------------------------- Declare Temp Table for Calculate Supplementary Insurance */
        DECLARE @SupplementaryInsurance AS TABLE
        (
            Id UNIQUEIDENTIFIER NOT NULL,
            GoodsCode VARCHAR(15) NOT NULL,
            DifferenceAmount MONEY
                DEFAULT (0),
            DifferencePaidAmount MONEY
                DEFAULT (0),
            PatientAmount MONEY
                DEFAULT (0),
            PatientPaidAmount MONEY
                DEFAULT (0),
            GoodsAmount MONEY
                DEFAULT (0),
            GoodsPaidAmount MONEY
                DEFAULT (0),
            NonInsuranceAmount MONEY,
            NonInsurancePaidAmount MONEY,
            EquipmentAmount MONEY,
            EquipmentPaidAmount MONEY,
            SupplementaryAmount MONEY
                DEFAULT (0),
            StatusCode TINYINT,
            IsMultipleDiffer BIT,
            IsMultiplePatient BIT,
            IsMultipleGoods BIT,
            IsMultipleNonInsurance BIT,
            IsSpecialDrug BIT
                DEFAULT (0),
            ApprovalCeilingAmount SMALLINT
                DEFAULT (0),
            MessageBody NVARCHAR(MAX)
        );
        /*--------------------------------------------------------------------------------------- Declare Temp Table for Calculate Supportive Companies*/
        DECLARE @SupportiveInsuranceTransaction AS TABLE
        (
            RowId UNIQUEIDENTIFIER NOT NULL,
            Id SMALLINT NOT NULL,
            InsuranceName NVARCHAR(30) NULL,
            PatientPaidAmount MONEY
                DEFAULT (0),
            DifferencePaidAmount MONEY
                DEFAULT (0),
            TransactionId SMALLINT,
            Kind TINYINT NULL
        );
        /*--------------------------------------------------------------------------------------- Declare Temp Table for Calculate Tax */
        DECLARE @TaxTransaction AS TABLE
        (
            RowId UNIQUEIDENTIFIER NOT NULL,
            PercentageAmount TINYINT NOT NULL,
            PriceAmount DECIMAL(19, 0) NOT NULL,
            TransactionId SMALLINT
        );
        /*--------------------------------------------------------------------------------------- Get Default Tax Percent */
        SELECT TOP (1)
               @DefaultTaxPercentage = Max_Percent,
               @TaxTransactionId = AutoId,
               @TaxTransactionName = [Name]
        FROM dbo.Over_Under
        WHERE Action_Code IN ( '-4', '-5' )
              AND Active = 1
              AND [Type] = 1
        ORDER BY AutoId DESC;
        /*--------------------------------------------------------------------------------------- Add Subscriptions item to Declared Table - Prepare Prescription Detail */
        INSERT INTO @PrescriptionDetail
        EXEC [dbo].[EPMPreCalculateSupplementaryInsurance] @PrescriptionType = @PrescriptionType,
                                                           @PrescriptionId = @PrescriptionId,
                                                           @PyramidPrescriptionTempId = @PyramidPrescriptionTempId,
                                                           @IncurableTransactionId = @IncurableTransactionId;
        --SELECT *
        --FROM @PrescriptionDetail;
        /*--------------------------------------------------------------------------------------- Currect (-1) Diifer of Insured  Amount  */
        UPDATE @PrescriptionDetail
        SET InsuredPaidAmount = ROUND(InsuredAmount, 0)
        WHERE ROUND(InsuredAmount - InsuredPaidAmount, 0) = 1;
        /*--------------------------------------------------------------------------------------- Get Supplementary Insurance Rules*/
        SELECT @NonInsuranceFlag = NonInsuranceFlag,
               @PatientFlag = PatientFlag,
               @GoodsFlag = GoodsFlag,
               @DifferFlag = DifferFlag,
               @SupplementaryTransactionId = TransactionId
        FROM dbo.SupplementaryInsurance
        WHERE Code = @InsuranceCode;
        IF (@FormulaDefinition IS NOT NULL AND @FormulaDefinition <> '')
        BEGIN
            /*--------------------------------------------------------------------------------------- Calc By Supplementary Insurance Pharmacopoeia Rules*/
            INSERT INTO @SupplementaryInsurance
            (
                Id,
                GoodsCode,
                DifferenceAmount,
                PatientAmount,
                GoodsAmount,
                NonInsuranceAmount,
                EquipmentAmount,
                SupplementaryAmount,
                StatusCode,
                IsMultipleDiffer,
                IsMultiplePatient,
                IsMultipleGoods,
                IsMultipleNonInsurance,
                IsSpecialDrug,
                ApprovalCeilingAmount,
                MessageBody
            )
            SELECT Id,
                   GoodsCode,
                   ISNULL(DifferAmount, 0),
                   ISNULL(PatientAmount, 0),
                   ISNULL(GoodsAmount, 0),
                   ISNULL(NonInsuranceAmount, 0),
                   ISNULL(EquipmentAmount, 0),
                   ISNULL(SupplementaryAmount, 0),
                   StatusCode,
                   IsMultipleDiffer,
                   IsMultiplePatient,
                   ISMultipleGoods,
                   ISMultipleNonInsurance,
                   ISNULL(IsSpecialDrug, 0),
                   ISNULL(Ceilling, 0),
                   MessageBody
            FROM
            (
                SELECT D.Id,
                       S.InsuranceCode,
                       D.GoodsCode,
                       CASE /*------------------------------------------------------------------- Calc Differ Amount*/
                           WHEN ISNULL(S.DifferAmount, 0) = 0 THEN
                               0
                           WHEN ISNULL(S.DifferAmount, 0) > 99
                                AND D.DifferenceAmount > 0 THEN
                       (CASE
                            WHEN (ISNULL(S.DifferAmount, 0) <= (D.DifferenceAmount / D.DeliverCount))
                                 AND
                                 (
                                     S.Ceilling = 0
                                     OR S.Ceilling >= D.DeliverCount
                                 ) THEN
                                ISNULL(S.DifferAmount, 0) * D.DeliverCount
                            WHEN (ISNULL(S.DifferAmount, 0) <= (D.DifferenceAmount / D.DeliverCount))
                                 AND
                                 (
                                     S.Ceilling <> 0
                                     AND S.Ceilling < D.DeliverCount
                                 ) THEN
                                ISNULL(S.DifferAmount, 0) * S.Ceilling
                            ELSE
                                D.DifferenceAmount
                        END
                       )
                           WHEN ISNULL(S.DifferAmount, 0) = 1
                                AND D.DifferenceAmount > 0 THEN
                       (CASE
                            WHEN S.Ceilling = 0
                                 OR S.Ceilling >= D.DeliverCount THEN
                                D.DifferenceAmount
                            WHEN S.Ceilling <> 0
                                 AND S.Ceilling < D.DeliverCount THEN
                                S.Ceilling * (D.DifferenceAmount / D.DeliverCount)
                            ELSE
                                0
                        END
                       )
                           WHEN ISNULL(S.DifferAmount, 0)
                                BETWEEN 2 AND 99
                                AND D.DifferenceAmount > 0 THEN
                       (CASE
                            WHEN S.Ceilling = 0
                                 OR S.Ceilling >= D.DeliverCount THEN
                                S.DifferAmount * D.DifferenceAmount * .01
                            WHEN S.Ceilling <> 0
                                 AND S.Ceilling < D.DeliverCount THEN
                                S.Ceilling * ((S.DifferAmount * D.DifferenceAmount) * .01 / D.DeliverCount)
                            ELSE
                                0
                        END
                       )
                           ELSE
                               0
                       END AS DifferAmount,
                       CASE
                           WHEN D.StatusCode = 2 THEN
                               0
                           ELSE
                               D.InsuredAmount
                       END InsuredAmount,
                       CASE
                           WHEN D.StatusCode <> 2 THEN
                               CASE /*--------------------------------------------------------------- Calc Patient Amount*/
                                   WHEN ISNULL(S.PatientAmount, 0) = 0 THEN
                                       0
                                   WHEN ISNULL(S.PatientAmount, 0) > 99
                                        AND ISNULL(S.PatientAmount, 0) <> 1 THEN
                       (CASE /*Start Case 01*/
                            WHEN (S.PatientAmount <= D.InsuredAmount - ISNULL(D.IncurablePaidAmount, 0))
                                 AND
                                 (
                                     S.Ceilling = 0
                                     OR S.Ceilling >= D.DeliverCount
                                 )
                                 AND
                                 (
                                     (
                                         S.Ceilling = 0
                                         AND S.PatientAmount * D.DeliverCount <= D.InsuredAmount
                                     )
                                     OR
                                     (
                                         S.Ceilling >= D.DeliverCount
                                         AND S.PatientAmount * D.DeliverCount <= D.InsuredAmount
                                     )
                                 ) THEN
                                S.PatientAmount * D.DeliverCount
                            WHEN (S.PatientAmount <= D.InsuredAmount - ISNULL(D.IncurablePaidAmount, 0))
                                 AND
                                 (
                                     S.Ceilling <> 0
                                     AND S.Ceilling < D.DeliverCount
                                 )
                                 AND
                                 (
                                     (
                                         S.Ceilling <> 0
                                         AND S.PatientAmount * S.Ceilling <= D.InsuredAmount
                                     )
                                     OR
                                     (
                                         S.Ceilling >= D.DeliverCount
                                         AND S.PatientAmount * S.Ceilling <= D.InsuredAmount
                                     )
                                 ) THEN
                                S.PatientAmount * S.Ceilling
                            ELSE
                                D.InsuredAmount - ISNULL(D.IncurablePaidAmount, 0)
                        END /*End Case 01*/
                       )
                                   WHEN ISNULL(S.PatientAmount, 0) = 1 THEN /*------------------- Full Pay - 100 Percent Pay*/
                       (CASE /*Start Case 02*/
                            WHEN S.Ceilling = 0
                                 OR S.Ceilling >= D.DeliverCount THEN
                                D.InsuredAmount - ISNULL(D.IncurablePaidAmount, 0) -----1403
                            WHEN S.Ceilling <> 0
                                 AND S.Ceilling < D.DeliverCount
                                 AND (D.InsuredAmount - ISNULL(D.IncurablePaidAmount, 0)) * S.PatientAmount * .01 >= D.InsuredAmount THEN
                                D.InsuredAmount
                            WHEN S.Ceilling <> 0
                                 AND S.Ceilling < D.DeliverCount THEN
                                D.SalesPriceAmount * S.Ceilling * D.InsuredFranshiz * .01
                                - (ISNULL(D.IncurablePaidAmount, 0) / D.DeliverCount)
                            ELSE
                                0
                        END /*End Case 02*/
                       )
                                   WHEN ISNULL(S.PatientAmount, 0)/*-------------------------   Pay Declared Price Amount */
                                        BETWEEN 2 AND 99 THEN
                       (CASE /*Start Case 03*/
                            WHEN S.Ceilling = 0
                                 OR S.Ceilling >= D.DeliverCount THEN
                       (D.InsuredAmount - ISNULL(D.IncurablePaidAmount, 0)) * S.PatientAmount * .01
                            WHEN S.Ceilling <> 0
                                 AND S.Ceilling < D.DeliverCount THEN
                       (D.SalesPriceAmount * S.Ceilling * D.InsuredFranshiz * .01
                        - (ISNULL(D.IncurablePaidAmount, 0) / D.DeliverCount)
                       ) * S.PatientAmount * .01
                            ELSE
                                0
                        END /*End Case 03*/
                       )
                                   ELSE
                                       0
                               END
                           ELSE
                               0
                       END AS PatientAmount,
                       CASE /*------------------------------------------------------------------Calc Goods Amount*/
                           WHEN D.StatusCode = 1 THEN
                       (CASE
                            WHEN ISNULL(S.GoodsAmount, 0) = 0 THEN
                                0
                            WHEN ISNULL(S.GoodsAmount, 0) > 99 THEN
                       (CASE
                            WHEN (ISNULL(S.GoodsAmount, 0) <= D.TotalPrice)
                                 AND
                                 (
                                     S.Ceilling = 0
                                     OR S.Ceilling >= D.DeliverCount
                                 ) THEN
                                ISNULL(S.GoodsAmount, 0) * D.DeliverCount
                            WHEN (ISNULL(S.GoodsAmount, 0) <= D.TotalPrice)
                                 AND
                                 (
                                     S.Ceilling <> 0
                                     AND S.Ceilling < D.DeliverCount
                                 ) THEN
                                ISNULL(S.GoodsAmount, 0) * S.Ceilling
                            ELSE
                                D.TotalPrice
                        END
                       )
                            WHEN ISNULL(S.GoodsAmount, 0) = 1 THEN
                       (CASE
                            WHEN S.Ceilling = 0
                                 OR S.Ceilling >= D.DeliverCount THEN
                                D.TotalPrice
                            WHEN S.Ceilling <> 0
                                 AND S.Ceilling < D.DeliverCount THEN
                                S.Ceilling * D.SalesPriceAmount
                            ELSE
                                0
                        END
                       )
                            WHEN ISNULL(S.GoodsAmount, 0) ------>Yazdian
                                 BETWEEN 2 AND 99 THEN
                       (CASE
                            WHEN S.Ceilling = 0
                                 OR S.Ceilling >= D.DeliverCount THEN
                                D.TotalPrice * S.GoodsAmount * .01
                            WHEN S.Ceilling <> 0
                                 AND S.Ceilling < D.DeliverCount THEN
                                D.SalesPriceAmount * S.Ceilling * S.GoodsAmount * .01
                            ELSE
                                0
                        END
                       )
                            ELSE
                                0
                        END
                       )
                           ELSE
                               0
                       END AS GoodsAmount,
                       CASE /*----------------------------------------------------------Calc NonInsurance Amount  */
                           WHEN D.StatusCode = 0 THEN
                       (CASE
                            WHEN ISNULL(S.NonInsuranceAmount, 0) = 0 THEN
                                0
                            WHEN ISNULL(S.NonInsuranceAmount, 0) > 99 THEN
                       (CASE
                            WHEN (ISNULL(S.NonInsuranceAmount, 0) <= D.TotalPrice)
                                 AND
                                 (
                                     S.Ceilling = 0
                                     OR S.Ceilling >= D.DeliverCount
                                 ) THEN
                                ISNULL(S.NonInsuranceAmount, 0) * D.DeliverCount
                            WHEN (ISNULL(S.NonInsuranceAmount, 0) <= D.TotalPrice)
                                 AND
                                 (
                                     S.Ceilling <> 0
                                     AND S.Ceilling < D.DeliverCount
                                 ) THEN
                                ISNULL(S.NonInsuranceAmount, 0) * S.Ceilling
                            ELSE
                                D.TotalPrice
                        END
                       )
                            WHEN ISNULL(S.NonInsuranceAmount, 0) = 1 THEN
                       (CASE
                            WHEN S.Ceilling = 0
                                 OR S.Ceilling >= D.DeliverCount THEN
                                D.TotalPrice
                            WHEN S.Ceilling <> 0
                                 AND S.Ceilling < D.DeliverCount THEN
                                S.Ceilling * D.SalesPriceAmount
                            ELSE
                                0
                        END
                       )
                            WHEN ISNULL(S.NonInsuranceAmount, 0)
                                 BETWEEN 2 AND 99 THEN
                       (CASE
                            WHEN S.Ceilling = 0
                                 OR S.Ceilling >= D.DeliverCount THEN
                                D.TotalPrice * S.NonInsuranceAmount * .01
                            WHEN S.Ceilling <> 0
                                 AND S.Ceilling < D.DeliverCount THEN
                                D.SalesPriceAmount * S.Ceilling * S.NonInsuranceAmount * .01
                            ELSE
                                0
                        END
                       )
                            ELSE
                                0
                        END
                       )
                           ELSE
                               0
                       END AS NonInsuranceAmount,
                       CASE
                           WHEN D.StatusCode = 2 THEN
                               D.InsuredPaidAmount
                           ELSE
                               0
                       END AS EquipmentAmount,
                       0 SupplementaryAmount,
                       D.StatusCode,
                       CASE
                           WHEN ISNULL(S.DifferAmount, 0)
                                BETWEEN 2 AND 99 THEN
                               1
                           ELSE
                               0
                       END AS IsMultipleDiffer,
                       CASE
                           WHEN ISNULL(S.PatientAmount, 0)
                                BETWEEN 2 AND 99 THEN
                               1
                           ELSE
                               0
                       END AS IsMultiplePatient,
                       CASE
                           WHEN ISNULL(S.GoodsAmount, 0)
                                BETWEEN 2 AND 99 THEN
                               1
                           ELSE
                               0
                       END AS ISMultipleGoods,
                       CASE
                           WHEN ISNULL(S.NonInsuranceAmount, 0)
                                BETWEEN 2 AND 99 THEN
                               1
                           ELSE
                               0
                       END AS ISMultipleNonInsurance,
                       S.IsSpecialDrug,
                       S.Ceilling,
                       M.[Message] MessageBody
                FROM @PrescriptionDetail D
                    INNER JOIN dbo.SupplementaryInsurance_DrugCondition S
                        ON S.InsuranceCode = @InsuranceCode
                           AND S.GoodsCode = D.GoodsCode
                           AND D.IsIncludeSupplementaryInsurance = 1
                    LEFT JOIN dbo.OrganizationsMessage M
                        ON M.Id = S.MessageId
                           AND ISNULL(M.IsActive, 0) = 1
            ) AS SupplementaryInsurance;
            /*--------------------------------------------------------------------------------------- Calc By Supplementary Insurance  Rules*/
            INSERT INTO @SupplementaryInsurance
            (
                Id,
                GoodsCode,
                DifferenceAmount,
                PatientAmount,
                GoodsAmount,
                NonInsuranceAmount,
                EquipmentAmount,
                SupplementaryAmount,
                StatusCode,
                IsMultipleDiffer,
                IsMultiplePatient,
                IsMultipleGoods,
                IsMultipleNonInsurance,
                IsSpecialDrug
            )
            SELECT D.Id,
                   D.GoodsCode,
                   CASE
                       WHEN D.StatusCode = 7 THEN
                           0
                       WHEN @DifferFlag = 0
                            AND D.DifferenceAmount > 0 THEN
                           0
                       WHEN @DifferFlag = 1
                            AND DifferenceAmount > 0 THEN
                           DifferenceAmount
                       ELSE
                           0
                   END DifferAmount,
                   CASE
                       WHEN D.StatusCode = 7 THEN
                           0
                       WHEN @PatientFlag = 0
                            AND D.StatusCode IN ( 5, 6, 8, 9 ) THEN
                           0
                       WHEN @PatientFlag = 1
                            AND D.StatusCode IN ( 5, 6, 8, 9 ) THEN
                           D.InsuredAmount - ISNULL(D.IncurablePaidAmount, 0)
                       ELSE
                           0
                   END PatientAmount,
                   CASE
                       WHEN D.StatusCode = 7 THEN
                           0
                       WHEN @GoodsFlag = 0
                            AND D.StatusCode = 1 THEN
                           0
                       WHEN @GoodsFlag = 1
                            AND D.StatusCode = 1 THEN
                           D.TotalPrice
                       ELSE
                           0
                   END GoodsAmount,
                   CASE
                       WHEN D.StatusCode = 7 THEN
                           0
                       WHEN @NonInsuranceFlag = 0
                            AND D.StatusCode = 0 THEN
                           0
                       WHEN @NonInsuranceFlag = 1
                            AND D.StatusCode = 0 THEN
                           D.TotalPrice
                       ELSE
                           0
                   END NonInsuranceAmount,
                   CASE
                       WHEN D.StatusCode = 2 THEN
                           D.InsuredPaidAmount
                       ELSE
                           0
                   END EquipmentAmount,
                   0,
                   D.StatusCode,
                   0,
                   0,
                   0,
                   0,
                   0
            FROM @PrescriptionDetail D
            WHERE D.Id NOT IN
                  (
                      SELECT Id
                      FROM @SupplementaryInsurance
                      WHERE D.IsIncludeSupplementaryInsurance = 1
                  )
                  AND D.IsIncludeSupplementaryInsurance = 1;
            --SELECT *
            --FROM @PrescriptionDetail;
            /*--------------------------------------------------------------------------------------- Update by Calculated Price */
            UPDATE @SupplementaryInsurance
            SET NonInsurancePaidAmount = ROUND(Calc.NonInsurancePaidAmount, 0),
                GoodsPaidAmount = ROUND(Calc.GoodsPaidAmount, 0),
                DifferencePaidAmount = ROUND(Calc.DifferencePaidAmount, 0),
                PatientPaidAmount = ROUND(Calc.PatientPaidAmount, 0),
                EquipmentPaidAmount = ROUND(Calc.EquipmentPaidAmount, 0),
                SupplementaryAmount = Calc.NonInsurancePaidAmount + Calc.GoodsPaidAmount + Calc.DifferencePaidAmount
                                      + Calc.PatientPaidAmount + Calc.EquipmentPaidAmount
            FROM @SupplementaryInsurance S
                INNER JOIN
                (
                    SELECT StatusCode,
                           GoodsCode,
                           Id,
                           CASE
                               WHEN IsMultipleNonInsurance = 1 THEN
                                   ISNULL(NonInsuranceAmount, 0)
                               ELSE
                                   REPLACE(
                                              SUBSTRING(@FormulaDefinition, 0, CHARINDEX('A', @FormulaDefinition) - 1),
                                              '0.',
                                              ''
                                          ) * ISNULL(NonInsuranceAmount, 0) * .01
                           END NonInsurancePaidAmount,
                           CASE
                               WHEN IsMultipleGoods = 1 THEN
                                   GoodsAmount
                               ELSE
                                   REPLACE(
                                              SUBSTRING(
                                                           @FormulaDefinition,
                                                           CHARINDEX('A', @FormulaDefinition) + 2,
                                                           CHARINDEX('B', @FormulaDefinition)
                                                           - CHARINDEX('A', @FormulaDefinition) - 3
                                                       ),
                                              '0.',
                                              ''
                                          ) * ISNULL(GoodsAmount, 0) * .01
                           END GoodsPaidAmount,
                           CASE
                               WHEN IsMultipleDiffer = 1 THEN
                                   DifferenceAmount
                               ELSE
                                   REPLACE(
                                              SUBSTRING(
                                                           @FormulaDefinition,
                                                           CHARINDEX('B', @FormulaDefinition) + 2,
                                                           CHARINDEX('C', @FormulaDefinition)
                                                           - CHARINDEX('B', @FormulaDefinition) - 3
                                                       ),
                                              '0.',
                                              ''
                                          ) * ISNULL(DifferenceAmount, 0) * .01
                           END DifferencePaidAmount,
                           CASE
                               WHEN IsMultiplePatient = 1 THEN
                                   PatientAmount
                               ELSE
                                   REPLACE(
                                              SUBSTRING(
                                                           @FormulaDefinition,
                                                           CHARINDEX('C', @FormulaDefinition) + 2,
                                                           CHARINDEX('D', @FormulaDefinition)
                                                           - CHARINDEX('C', @FormulaDefinition) - 3
                                                       ),
                                              '0.',
                                              ''
                                          ) * ISNULL(PatientAmount, 0) * .01
                           END PatientPaidAmount,
                           REPLACE(
                                      SUBSTRING(
                                                   @FormulaDefinition,
                                                   CHARINDEX('D', @FormulaDefinition) + 2,
                                                   CHARINDEX('E', @FormulaDefinition)
                                                   - CHARINDEX('D', @FormulaDefinition) - 3
                                               ),
                                      '0.',
                                      ''
                                  ) * ISNULL(EquipmentAmount, 0) * .01 EquipmentPaidAmount
                    FROM @SupplementaryInsurance
                ) AS Calc
                    ON Calc.GoodsCode = S.GoodsCode
                       AND Calc.Id = S.Id
                       AND S.StatusCode = Calc.StatusCode;
        END;

        /*----------------------------------------------------------------------------------------- Calculate Supportive Companies Row */
        INSERT INTO @SupportiveInsuranceTransaction
        (
            RowId,
            Id,
            InsuranceName,
            TransactionId,
            PatientPaidAmount,
            DifferencePaidAmount
        )
        SELECT D.Id,
               T.AutoId,
               T.[Name],
               S.IncrementalDeductionId,
               SUM(ROUND(
                            CASE
                                WHEN S.PatientPercent > 0 /*------------------------------------ Calc By Percent */
                                     AND D.InsuredFranshiz <> 100 THEN
                                    CASE
                                        WHEN D.InsuredFranshiz = 0
                                             AND D.StatusCode = 0 THEN
                            ((D.SalesPriceAmount * D.DeliverCount)
                             - (ISNULL(SI.NonInsurancePaidAmount, 0) + ISNULL(D.IncurablePaidAmount, 0))
                            )
                            * S.PatientPercent * .01
                                        WHEN D.InsuredFranshiz = 0
                                             AND D.StatusCode IN ( 4, 5, 6 ) THEN
                                            0
                                        ELSE
                            ((D.SalesPriceAmount * D.DeliverCount * D.InsuredFranshiz * .01)
                             - (ISNULL(SI.PatientPaidAmount, 0) + ISNULL(D.IncurablePaidAmount, 0))
                            ) * S.PatientPercent
                            * .01
                                    END
                                WHEN ISNULL(S.PatientPrice, 0) > 0 /*------------------------------------ Calc By Price  */
                                     AND D.InsuredFranshiz <> 100 THEN
                                    CASE
                                        WHEN D.InsuredFranshiz = 0 /*---------------------------- Total Insured Remain Amount VS Patient Price Of Supportive*/
                                             AND D.StatusCode = 0
                                             AND D.InsuredPaidAmount
                                                 - (ISNULL(SI.NonInsurancePaidAmount, 0)
                                                    + ISNULL(D.IncurablePaidAmount, 0)
                                                   ) < S.PatientPrice THEN
                                            D.InsuredPaidAmount
                                            - (ISNULL(SI.NonInsurancePaidAmount, 0) + ISNULL(D.IncurablePaidAmount, 0))
                                        WHEN D.InsuredFranshiz = 0 /*---------------------------- Total Insured Remain Amount VS Total Patient Price Of Supportive*/
                                             AND D.StatusCode = 0
                                             AND D.InsuredPaidAmount
                                                 - (ISNULL(SI.NonInsurancePaidAmount, 0)
                                                    + ISNULL(D.IncurablePaidAmount, 0)
                                                   ) < S.PatientPrice
                                                       * D.DeliverCount THEN
                                            D.InsuredPaidAmount
                                            - (ISNULL(SI.NonInsurancePaidAmount, 0) + ISNULL(D.IncurablePaidAmount, 0))
                                        WHEN D.InsuredFranshiz = 0
                                             AND D.StatusCode = 0
                                             AND D.InsuredPaidAmount
                                                 - (ISNULL(SI.NonInsurancePaidAmount, 0)
                                                    + ISNULL(D.IncurablePaidAmount, 0)
                                                   ) >= S.PatientPrice
                                                        * D.DeliverCount THEN
                                            S.PatientPrice * D.DeliverCount
                                        WHEN D.InsuredFranshiz = 0
                                             AND D.StatusCode = 0
                                             AND D.InsuredPaidAmount
                                                 - (ISNULL(SI.NonInsurancePaidAmount, 0)
                                                    + ISNULL(D.IncurablePaidAmount, 0)
                                                   ) >= S.PatientPrice THEN
                                            S.PatientPrice
                                        WHEN D.InsuredFranshiz = 0
                                             AND D.StatusCode IN ( 4, 5, 6 ) THEN
                                            0
                                        WHEN (D.SalesPriceAmount * D.DeliverCount * D.InsuredFranshiz * .01)
                                             - (ISNULL(SI.PatientPaidAmount, 0) + ISNULL(D.IncurablePaidAmount, 0)) > S.PatientPrice
                                                                                                                      * D.DeliverCount THEN
                                            S.PatientPrice * D.DeliverCount
                                        ELSE
                            (D.SalesPriceAmount * D.DeliverCount * D.InsuredFranshiz * .01)
                            - (ISNULL(SI.PatientPaidAmount, 0) + ISNULL(D.IncurablePaidAmount, 0))
                                    END
                                ELSE
                                    0
                            END,
                            0
                        )
                  ) AS PatientPaidAmount,
               SUM(ROUND(
                            CASE /*------------------------------------ Calc Difference Price Amnount */
                                WHEN S.DifferPercent > 0
                                     AND D.DifferenceAmount > 0 THEN
                                    CASE
                                        WHEN S.DifferPercent * D.DifferenceAmount * .01 >= D.DifferenceAmount THEN
                                            D.DifferenceAmount - ISNULL(SI.DifferencePaidAmount, 0)
                                        ELSE
                            (D.DifferenceAmount - ISNULL(SI.DifferencePaidAmount, 0)) * S.DifferPercent * .01
                                    END
                                WHEN S.DifferPrice > 0
                                     AND D.DifferenceAmount > 0 THEN
                                    CASE
                                        WHEN (S.DifferPrice * D.DeliverCount) <= D.DifferenceAmount
                                                                                 - ISNULL(SI.DifferencePaidAmount, 0) THEN
                                            S.DifferPrice * D.DeliverCount
                                        ELSE
                                            D.DifferenceAmount - ISNULL(SI.DifferencePaidAmount, 0)
                                    END
                                ELSE
                                    0
                            END,
                            0
                        )
                  ) AS DifferencePaidAmount
        FROM @PrescriptionDetail D
            JOIN dbo.GoodsSupportiveCompanies S
                ON S.GoodsCode = D.GoodsCode
            JOIN dbo.Over_Under T
                ON T.AutoId = S.IncrementalDeductionId
            LEFT JOIN
            (
                SELECT Id,
                       GoodsCode,
                       SUM(PatientPaidAmount) PatientPaidAmount,
                       SUM(DifferencePaidAmount) DifferencePaidAmount,
                       SUM(NonInsurancePaidAmount) NonInsurancePaidAmount
                FROM @SupplementaryInsurance
                GROUP BY Id,
                         GoodsCode
            ) AS SI
                ON SI.Id = D.Id
                   AND SI.GoodsCode = D.GoodsCode
        WHERE D.StatusCode NOT IN ( 3, 8 )
              AND D.StatusCode > CASE
                                     WHEN S.Kind = 0 THEN
                                         -1
                                     ELSE
                                         0
                                 END
        GROUP BY D.Id,
                 T.[Name],
                 T.AutoId,
                 S.IncrementalDeductionId;

        /*--------------------------------------------------------------------------------------- Correct (-1) after round calculated value  in  Supportive Insurance Transaction*/
        UPDATE @SupportiveInsuranceTransaction
        SET PatientPaidAmount = 0
        WHERE PatientPaidAmount = -1;

        UPDATE @SupportiveInsuranceTransaction
        SET DifferencePaidAmount = 0
        WHERE DifferencePaidAmount = -1;
        /*--------------------------------------------------------------------------------------- Calculate Tax Transaction Row */
        IF EXISTS (SELECT 1 FROM @PrescriptionDetail D WHERE D.HasDuty = 1)
        BEGIN

            INSERT INTO @TaxTransaction
            (
                RowId,
                PercentageAmount,
                PriceAmount,
                TransactionId
            )
            SELECT D.Id,
                   CASE
                       WHEN ISNULL(MAX(G.TaxPercent), 0) = 0 THEN
                           @DefaultTaxPercentage
                       ELSE
                           MAX(G.TaxPercent)
                   END,
                   SUM(D.TotalPrice) * CASE
                                           WHEN ISNULL(MAX(G.TaxPercent), 0) = 0 THEN
                                               @DefaultTaxPercentage
                                           ELSE
                                               MAX(G.TaxPercent)
                                       END * .01,
                   @TaxTransactionId
            FROM @PrescriptionDetail D
                JOIN dbo.KalaId G
                    ON G.K_Code = D.GoodsCode
            WHERE D.HasDuty = 1
            GROUP BY D.Id,
                     D.GoodsCode;
        END;
        /*--------------------------------------------------------------------------------------- Select Final Result*/
        IF @PrescriptionType = 4
        BEGIN
            INSERT INTO dbo.Tmp_Over_Under_Row_Drug
            (
                PrescriptionId,
                Code_Over_Under,
                Code_Act,
                Kind_Act,
                Price,
                [Percent],
                [Name],
                [Type]
            )
            SELECT FinalCalculated.RowId,
                   FinalCalculated.TransactionId,
                   10,
                   10,
                   FinalCalculated.Amount,
                   0,
                   TR.[Name] TransactionName,
                   0
            FROM
            (
                SELECT CAST(D.NonInsuranceAmount AS INT) RowId,
                       D.GoodsCode,
                       G.Name1 DrugName,
                       Amount = CASE Crossed.Counting
                                    WHEN 1 THEN
                                        ISNULL(S.Amount, 0)
                                    WHEN 2 THEN
                                        SI.Amount
                                    ELSE
                                        0
                                END,
                       TransactionId = CASE Crossed.Counting
                                           WHEN 1 THEN
                                               @SupplementaryTransactionId
                                           WHEN 2 THEN
                                               SI.TransactionId
                                           ELSE
                                               0
                                       END
                FROM @PrescriptionDetail D
                    JOIN dbo.KalaId G
                        ON G.K_Code = D.GoodsCode
                    OUTER APPLY
                (
                    SELECT S.SupplementaryAmount Amount
                    FROM @SupplementaryInsurance S
                    WHERE S.Id = D.Id
                ) AS S
                    OUTER APPLY
                (
                    SELECT T.TransactionId,
                           ISNULL(T.PatientPaidAmount, 0) + ISNULL(T.DifferencePaidAmount, 0) Amount
                    FROM @SupportiveInsuranceTransaction T
                    WHERE T.RowId = D.Id
                ) AS SI
                    CROSS JOIN
                    (SELECT 1 UNION SELECT 2) AS Crossed(Counting)
            ) AS FinalCalculated
                JOIN dbo.Over_Under TR
                    ON TR.AutoId = FinalCalculated.TransactionId
            WHERE FinalCalculated.Amount > 0;
            /*--------------------------------------------------------------------------------------- Update Header of Transaction From Detail*/
            UPDATE TransactionInvoice
            SET Price = Summary.Amount
            FROM dbo.Tmp_Over_Under_Factor_Drug TransactionInvoice
                JOIN
                (
                    SELECT SUM(Price) Amount,
                           MIN(Code_Over_Under) Code
                    FROM dbo.Tmp_Over_Under_Row_Drug
                    WHERE PrescriptionId IN
                          (
                              SELECT AutoId
                              FROM dbo.TmpDrugHavaleh
                              WHERE Id_Havaleh = @PyramidPrescriptionTempId
                          )
                          AND
                          (
                              (
                                  Kind_Act = 10
                                  AND Code_Act = 10
                                  AND Code_Over_Under <> ISNULL(@SupplementaryTransactionId, 0)
                              )
                              OR (Code_Over_Under = ISNULL(@IncurableTransactionId, 0))
                          )
                          AND Price > 0
                    GROUP BY Code_Over_Under
                ) AS Summary
                    ON Summary.Code = TransactionInvoice.Code_Over_Under
                       AND TransactionInvoice.Id_Havaleh = @PyramidPrescriptionTempId;
            /*--------------------------------------------------------------------------------------- Select Result Value*/
            SELECT ISNULL(SUM(D.IncurablePaidAmount), 0) IncurableTotalAmount,
                   ROUND(ISNULL(SUM(S.Amount), 0), 0) SupplementaryTotalAmount,
                   ISNULL(SUM(SI.Amount), 0) SupportiveInsuranceTotalAmount,
                   @SupplementaryTransactionId SupplementaryTransactionId,
                   @IncurableTransactionId IncurableTransactionId
            FROM @PrescriptionDetail D
                OUTER APPLY
            (
                SELECT S.SupplementaryAmount Amount
                FROM @SupplementaryInsurance S
                WHERE S.Id = D.Id
            ) AS S
                OUTER APPLY
            (
                SELECT T.TransactionId,
                       ISNULL(T.PatientPaidAmount, 0) + ISNULL(T.DifferencePaidAmount, 0) Amount
                FROM @SupportiveInsuranceTransaction T
                    JOIN dbo.GoodsSupportiveCompanies GT
                        ON GT.GoodsCode = D.GoodsCode
                WHERE T.RowId = D.Id
            ) AS SI;
        END;
        ELSE
        /*--------------------------------------------------------------------------------------- Insert into Transaction For EPM Prescription After Save */
        IF @PrescriptionType = 3
           AND
           (
               @FormulaDefinition IS NULL
               OR @FormulaDefinition = ''
           )
        BEGIN
            DECLARE @SupportiveInsuranceAmount MONEY = 0;
            DECLARE @TaxtTotalAmount MONEY = 0;

            SELECT @SupportiveInsuranceAmount
                = SUM(ISNULL(SI.PatientPaidAmount, 0) + ISNULL(SI.DifferencePaidAmount, 0))
            FROM @SupportiveInsuranceTransaction SI;

            SELECT @TaxtTotalAmount = SUM(ISNULL(T.PriceAmount, 0))
            FROM @TaxTransaction T;

            IF ISNULL(@SupportiveInsuranceAmount, 0) > 0
            BEGIN
                DELETE FROM dbo.EPMPrescriptionTransaction
                WHERE PrescriptionId = @PrescriptionId;

                DECLARE @NewId UNIQUEIDENTIFIER = NEWID();

                INSERT INTO dbo.EPMPrescriptionTransaction
                (
                    Id,
                    PrescriptionTypeCode,
                    PrescriptionId,
                    IncurableTotalAmount,
                    SupplementaryInsuranceTotalAmount,
                    SupportiveInsuranceTotalAmount,
                    TaxTotalAmount,
                    CreatedBy
                )
                VALUES
                (@NewId, 3, @PrescriptionId, 0, 0, @SupportiveInsuranceAmount, @TaxtTotalAmount, 100);

                INSERT INTO dbo.EPMPrescriptionTransactionDetail
                (
                    Id,
                    PrescriptionTransactionId,
                    PrescriptionDetailId,
                    TransactionType,
                    TransactionId,
                    Amount
                )
                SELECT NEWID(),
                       @NewId,
                       D.Id,
                       3,
                       SI.TransactionId,
                       MAX(ISNULL(SI.PatientPaidAmount, 0) + ISNULL(SI.DifferencePaidAmount, 0))
                FROM @PrescriptionDetail D
                    JOIN @SupportiveInsuranceTransaction SI
                        ON SI.RowId = D.Id
                GROUP BY D.Id,
                         SI.TransactionId;

                INSERT INTO dbo.EPMPrescriptionTransactionDetail
                (
                    Id,
                    PrescriptionTransactionId,
                    PrescriptionDetailId,
                    TransactionType,
                    TransactionId,
                    Amount
                )
                SELECT NEWID(),
                       @NewId,
                       D.Id,
                       4,
                       T.TransactionId,
                       MAX(ISNULL(T.PriceAmount, 0))
                FROM @PrescriptionDetail D
                    JOIN @TaxTransaction T
                        ON T.RowId = D.Id
                WHERE D.HasDuty = 1
                GROUP BY D.Id,
                         T.TransactionId;
            END;

            SELECT SI.SupportiveInsuranceTotalAmount,
                   ISNULL(SI.IncurableTotalAmount, 0) IncurableTotalAmount,
                   ISNULL(SI.TaxTotalAmount, 0) TaxTotalAmount
            FROM dbo.EPMPrescriptionTransaction SI
            WHERE SI.PrescriptionId = @PrescriptionId;
        END;
        ELSE
        BEGIN
            /*-------------------------------------------------------------Calculate On detail */
            UPDATE @PrescriptionDetail
            SET InsuredAmount = CASE
                                    WHEN D.StatusCode = 2 THEN
                                        0
                                    ELSE
                                        D.InsuredAmount
                                END,
                NonInsuranceAmount = CASE
                                         WHEN D.InsuredPaidAmount - D.DifferenceAmount - D.InsuredAmount < 0
                                              OR D.StatusCode IN ( 1, 2 ) THEN
                                             0
                                         ELSE
                                             D.InsuredPaidAmount - D.DifferenceAmount - D.InsuredAmount
                                     END,
                EquipmentAmount = CASE
                                      WHEN D.StatusCode = 2 THEN
                                          D.InsuredPaidAmount
                                      ELSE
                                          0
                                  END,
                GoodsAmount = CASE
                                  WHEN D.StatusCode = 1 THEN
                                      D.InsuredPaidAmount
                                  ELSE
                                      0
                              END
            FROM @PrescriptionDetail D;

            SELECT D.Id,
                   @PrescriptionId PrescriptionId,
                   CAST(D.RowIndex AS INT) RowIndex,
                   D.IsIncludeSupplementaryInsurance,
                   D.GoodsCode,
                   CASE
                       WHEN D.GenericCode IS NULL
                            OR D.GenericCode = '' THEN
                           G.Meli_Code
                       ELSE
                           D.GenericCode
                   END GenericCode,
                   CASE
                       WHEN D.IRCCode IS NULL
                            OR D.IRCCode = '' THEN
                           G.Barcode
                       ELSE
                           D.IRCCode
                   END IRCCode,
                   G.Name1 DrugName,
                   MAX(D.InsuredFranshiz) InsuredFranshiz,
                   SUM(D.TotalPrice) TotalPriceAmount,
                   CAST(SUM(D.DeliverCount) AS DEC(5, 2)) DeliverCount,
                   CAST(MAX(D.CoverCount) AS DEC(5, 0)) CoverCount,
                   MAX(D.DifferenceAmount) DifferenceAmount,
                   MAX(D.SalesPriceAmount) SalesPriceAmount,
                   MAX(D.InsuredAmount) InsuredAmount,
                   MAX(D.NonInsuranceAmount) NonInsuranceAmount,
                   MAX(D.EquipmentAmount) EquipmentAmount,
                   MAX(D.GoodsAmount) GoodsAmount,
                   MAX(D.InsuredPaidAmount) InsuredPaidAmount,
                   MAX(D.IncurablePaidAmount) IncurablePaidAmount,
                   @IncurableTransactionId IncurableTransactionId,
                   MAX(S.DifferencePaidAmount) SupplementaryDifferencePaidAmount,
                   MAX(S.PatientPaidAmount) SupplementaryPatientPaidAmount,
                   MAX(S.GoodsPaidAmount) SupplementaryGoodsPaidAmount,
                   MAX(S.NonInsurancePaidAmount) SupplementaryNonInsurancePaidAmount,
                   MAX(S.EquipmentPaidAmount) SupplementaryEquipmentPaidAmount,
                   MAX(D.StatusCode) DrugStatusCode,
                   S.IsSpecialDrug,
                   MAX(ISNULL(S.ApprovalCeilingAmount, 0)) SupplementaryApprovalCeilingAmount,
                   ISNULL(S.MessageBody, '') MessageBody,
                   MAX(S.SupplementaryAmount) SupplementaryAmount,
                   ISNULL(SI.InsuranceName, '') SupportiveInsuranceName,
                   ISNULL(SI.TransactionId, 0) SupportiveInsuranceTransactionId,
                   MAX(ISNULL(SI.PatientPaidAmount, 0)) SupportiveInsurancePatientPaidAmount,
                   MAX(ISNULL(SI.DifferencePaidAmount, 0)) SupportiveInsuranceDifferencePaidAmount,
                   MAX(D.InsuredPaidAmount) - ROUND(MAX(ISNULL(S.SupplementaryAmount, 0)), 0)
                   - (MAX(ISNULL(SI.DifferencePaidAmount, 0)) + MAX(ISNULL(SI.PatientPaidAmount, 0)))
                   - MAX(ISNULL(D.IncurablePaidAmount, 0)) + MAX(ISNULL(Tax.TransactionAmount, 0)) ExcessAmount,
                   MAX(D.DifferenceAmount) - MAX(ISNULL(S.DifferencePaidAmount, 0))
                   - MAX(ISNULL(SI.DifferencePaidAmount, 0)) ExcessDifferenceAmount,
                   CASE
                       WHEN MAX(D.InsuredAmount) > 0 THEN
                           MAX(D.InsuredAmount) - MAX(ISNULL(D.IncurablePaidAmount, 0))
                           - MAX(ISNULL(S.PatientPaidAmount, 0)) - MAX(ISNULL(SI.PatientPaidAmount, 0))
                       ELSE
                           0
                   END ExcessInsuredPaidAmount,
                   CASE
                       WHEN MAX(D.GoodsAmount) > 0 THEN
                           MAX(D.GoodsAmount) - MAX(ISNULL(S.GoodsPaidAmount, 0))
                           - MAX(ISNULL(SI.PatientPaidAmount, 0))
                       ELSE
                           0
                   END ExcessGoodsPaidAmount,
                   CASE
                       WHEN MAX(D.EquipmentAmount) > 0 THEN
                           MAX(D.EquipmentAmount) - MAX(ISNULL(S.EquipmentPaidAmount, 0))
                           - MAX(ISNULL(SI.PatientPaidAmount, 0))
                       ELSE
                           0
                   END ExcessEquipmentPaidAmount,
                   CASE
                       WHEN MAX(D.NonInsuranceAmount) > 0 THEN
                           MAX(D.NonInsuranceAmount) - MAX(ISNULL(S.NonInsurancePaidAmount, 0))
                           - MAX(ISNULL(SI.PatientPaidAmount, 0))
                       ELSE
                           0
                   END ExcessNonInsurancePaidAmount,
                   ISNULL(Tax.TransactionName, '') TaxTransactionName,
                   ISNULL(Tax.TransactionId, 0) TaxTransactionId,
                   MAX(ISNULL(Tax.TransactionAmount, 0)) TaxTransactionPriceAmount,
                   MAX(ISNULL(Tax.PercentageAmount, 0)) TaxTransactionPercentageAmount
            FROM @PrescriptionDetail D
                JOIN dbo.KalaId G
                    ON G.K_Code = D.GoodsCode
                OUTER APPLY
            (
                SELECT SUM(S.PatientAmount) PatientAmount,
                       SUM(S.DifferencePaidAmount) DifferencePaidAmount,
                       SUM(S.PatientPaidAmount) PatientPaidAmount,
                       SUM(S.GoodsPaidAmount) GoodsPaidAmount,
                       SUM(S.NonInsurancePaidAmount) NonInsurancePaidAmount,
                       SUM(S.EquipmentPaidAmount) EquipmentPaidAmount,
                       MAX(S.StatusCode) DrugStatusCode,
                       S.IsSpecialDrug,
                       MAX(S.ApprovalCeilingAmount) ApprovalCeilingAmount,
                       S.MessageBody,
                       SUM(S.SupplementaryAmount) SupplementaryAmount
                FROM @SupplementaryInsurance S
                WHERE S.Id = D.Id
                GROUP BY S.Id,
                         S.GoodsCode,
                         S.IsSpecialDrug,
                         S.MessageBody
            ) AS S /*------------------------ Join to Calculated Supplementary Insurance */
                OUTER APPLY
            (
                SELECT T.InsuranceName,
                       T.TransactionId,
                       ISNULL(T.PatientPaidAmount, 0) PatientPaidAmount,
                       ISNULL(T.DifferencePaidAmount, 0) DifferencePaidAmount
                FROM @SupportiveInsuranceTransaction T
                WHERE T.RowId = D.Id
            ) AS SI /*-------------------- Calculated Supportive Companies Insurance Saved*/
                OUTER APPLY
            (
                SELECT @TaxTransactionName TransactionName,
                       T.TransactionId,
                       ISNULL(T.PriceAmount, 0) TransactionAmount,
                       ISNULL(T.PercentageAmount, 0) PercentageAmount
                FROM @TaxTransaction T
                WHERE T.RowId = D.Id
            ) AS Tax /*-------------------- Calculated Tax Amount */
            GROUP BY D.Id,
                     D.IsIncludeSupplementaryInsurance,
                     D.GoodsCode,
                     D.GenericCode,
                     G.Barcode,
                     G.Name1,
                     D.RowIndex,
                     G.Meli_Code,
                     D.IRCCode,
                     S.IsSpecialDrug,
                     S.MessageBody,
                     SI.InsuranceName,
                     SI.TransactionId,
                     Tax.TransactionId,
                     Tax.TransactionName
            ORDER BY D.RowIndex;
        END;
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(MAX) = ERROR_MESSAGE();
        RAISERROR('        ʘ       [ 200- ]   ', 16, 1);
        DECLARE @JsonErrorBody VARCHAR(MAX);
        SELECT @JsonErrorBody
            = '['
              + STUFF(
        (
            SELECT ',{' + '"PrescriptionType":' + CAST(@PrescriptionType AS VARCHAR(3)) + ','
                   + COALESCE('"InsuranceCode":"' + @InsuranceCode + '",', '')
                   + COALESCE('"FormulaDefinition":"' + ISNULL(@FormulaDefinition, '-') + '",', '')
                   + COALESCE('"PrescriptionId":"' + CAST(@PrescriptionId AS VARCHAR(100)) + '",', '')
                   + COALESCE(
                                 '"PyramidPrescriptionTempId":'
                                 + CAST(ISNULL(@PyramidPrescriptionTempId, 0) AS VARCHAR(10)),
                                 ''
                             ) + '}'
            FOR XML PATH(''), TYPE
        ).value('.', 'varchar(MAX)'),
        1   ,
        1   ,
        ''
                     ) + ']';

        INSERT INTO dbo.IHIOActivityHistory
        (
            ActionCode,
            EntityName,
            JsonActivity,
            JsonResult,
            CreatedBy
        )
        VALUES
        (3, 'CalculateSupplementaryInsurance', @JsonErrorBody, @ErrorMessage, 100);

        PRINT ERROR_MESSAGE();
    END CATCH;
END;